package databaseHandler;
import java.sql.*;
import java.util.List;

public class Database
{
	private ConnectionPool pool;
	private Connection connection;
	public Database(ConnectionPool pool)
	{
		this.pool =pool;
	}
	private Connection getConnection() throws SQLException
	{
                if(connection==null||connection.isClosed())
                {
                    connection=pool.getConnection();
                }
		return connection;
	}
	private PreparedStatement getPreparedStatement(Connection connection,String sql)
	{
            PreparedStatement prepared =null;
            try
            {
                    prepared=connection.prepareStatement(sql);
            }
            catch(SQLException ex)
            {
                    System.out.println("can not make prepared statement");
                    System.out.println(ex.getMessage());
            }
            return prepared;
	}
	private boolean populateParameters(PreparedStatement preparedStatement,Object[] parameters)
	{
		boolean populated=false;
		try
			{
				//Object obj=null;
				String classType;
				if(parameters!=null)
				{
					for(int i=1;i<=parameters.length;i++)
					{
						preparedStatement.setObject(i,parameters[i]);
					}
				}
				populated=true;
			}
			catch(SQLException ex)
			{
				System.out.println("can not set the parameters");
				ex.printStackTrace();
			}
			return populated;
	}
	private PreparedStatement getReadyPreparedStatement(String query,Object[] parameters) throws SQLException
	{
		PreparedStatement preparedStatement=null;
		Connection connection=getConnection();
		if(connection!=null)
		{
			preparedStatement=getPreparedStatement(connection,query);
			if(preparedStatement!=null&&!populateParameters(preparedStatement,parameters))
			{
				preparedStatement=null;
			}
		}
		return preparedStatement;
	}
	
	public ResultSet excuteQuery(String query,Object[] parameters) throws SQLException
	{
		ResultSet resultSet=null;
		PreparedStatement preparedStatement = getReadyPreparedStatement(query,parameters);
		if(preparedStatement!=null)
		{
			try
			{
				resultSet=preparedStatement.executeQuery();
			}
			catch(SQLException ex)
			{
				System.out.println("errors in querying");
				ex.printStackTrace();
			}
		}
		return resultSet;
	}
        
        
	public int excuteUpdate(String query,Object[] parameters) throws SQLException
	{
		int result=-1;
		PreparedStatement preparedStatement = getReadyPreparedStatement(query,parameters);
		if(preparedStatement!=null)
		{
			try
			{
				result=preparedStatement.executeUpdate();
			}
			catch(SQLException ex)
			{
				System.out.println("errors in querying");
				ex.printStackTrace();
			}
		}
                ResultSet resultSet = preparedStatement.getResultSet();
                preparedStatement.close();
                resultSet.close();
		return result;
	}
        public int excuteUpdate(List<String> queries,List<Object[]> parameters) throws SQLException
	{
		int result=-1;
		PreparedStatement preparedStatement = getReadyPreparedStatement(queries.get(0),parameters.get(0));
                preparedStatement.getConnection().setAutoCommit(false);
                boolean done = true;
		if(preparedStatement!=null)
		{
                        for(int i=0;i<queries.size();i++)
                        {
                           try
                           {
                                   preparedStatement = getReadyPreparedStatement(queries.get(i),parameters.get(i));
                                   preparedStatement.executeUpdate();
                           }
                           catch(SQLException ex)
                           {
                                   System.out.println("errors in querying");
                                   done = false;
                                   ex.printStackTrace();
                           }
                        }
                }
                if(done)
                {
                    preparedStatement.getConnection().setAutoCommit(false);
                }
                ResultSet resultSet = preparedStatement.getResultSet();
                preparedStatement.close();
                resultSet.close();
		return result;
	}
	public void printResultSet(ResultSet resultSet)
	{
		try
		{
			ResultSetMetaData data=resultSet.getMetaData();
			int count=data.getColumnCount();
			//Object obj=null;
			while (resultSet.next()) {
				for(int i=1;i<=count;i++)
				{
					System.out.println(data.getColumnName(i)+" of type "+data.getColumnTypeName(i)+", with value "+resultSet.getObject(i));
				}
				System.out.println("*************************************");
			  }
		}
		catch(SQLException ex)
		{
			System.out.println("error in printing");
			ex.printStackTrace();
		}
	}
//	private void closePreparesStatement(Statement pr)
//	{
//		try
//		{
//			pr.close();
//		}
//		catch(SQLException ex)
//		{
//			System.out.println("error in closing prepared statement");
//			ex.printStackTrace();
//		}
//	}
//	private void closeResultSet(ResultSet set)
//	{
//		try
//		{
//			set.close();
//		}
//		catch(SQLException ex)
//		{
//			System.out.println("error in closing resultSet");
//			ex.printStackTrace();
//		}
//	}
    	public void closeConnection()
    	{
    		try
    		{
    			connection.close();
    		}
    		catch(SQLException ex)
    		{
    			System.out.println("error in closing connection");
    			ex.printStackTrace();
    		}
    	}
//	private void closeAll(ResultSet set)
//	{
//		try
//		{
//			Connection con=set.getStatement().getConnection();
//			closePreparesStatement(set.getStatement());
//			closeResultSet(set);
//			closeConnection(con);	
//		}
//		catch(SQLException ex)
//		{
//			ex.printStackTrace();
//		}
//	}
//	public static void main(String[] args)
//	{
//		Database database=new Database("jdbc:oracle:thin:@127.0.0.1:1521:XE","mahdi","12345678");
//		ResultSet result=database.excuteQuery("select * from teacher",null);
//		if(result!=null)
//		{
//			database.printResultSet(result);
//		}
//		
//		//database.excuteUpdate("update teacher set name='ahmed'",null);
//		
//	}
	
}